package com.jiuyun.servlet;

import com.jiuyun.dao.StuentDao;
import com.jiuyun.dao.imol.StuentDaoImol;
import com.jiuyun.entity.Student;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.List;

@WebServlet("/excel")
public class ExcelServlet extends HttpServlet {
    final  int MAX_FILE_SIZE=1024*1024*4;//4MB
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //文件上传后的位置    服务器的运行位置
        String appPath=req.getServletContext().getRealPath("/classes");
        System.out.println("运行位置"+appPath);
        //配置上传参数
        DiskFileItemFactory factory=new DiskFileItemFactory();
        //设置内存          设置最大文件上传值     默认2M
        ServletFileUpload upload=new ServletFileUpload(factory);
        //设置字符编码
        upload.setFileSizeMax(MAX_FILE_SIZE);
        upload.setHeaderEncoding("utf-8");
        String fname="";
        try {
            List<FileItem> formItems=upload.parseRequest(req);
            for(FileItem item:formItems){
                if(item.isFormField()){    //文本框
                    //?????
                }else{//文件
                    fname=item.getName();  //iphone.jsp
                    File file=new File(appPath+"/"+fname);
                    //保存文件
                    item.write(file);//存盘
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

            StuentDao studao=new StuentDaoImol();
            ClassLoader classLoader=ExcelServlet.class.getClassLoader();
            //
            String filename="hello.xlsx";
            InputStream input=classLoader.getResourceAsStream(filename);
            //打开excel
            XSSFWorkbook sheets=new XSSFWorkbook(input);
            XSSFSheet sheet=sheets.getSheetAt(0);
            //读取行数
            int rows=sheet.getPhysicalNumberOfRows();

            //读取列
            XSSFRow row=sheet.getRow(0);
            int cols=row.getPhysicalNumberOfCells();
            System.out.println(rows+""+cols);
            for (int i=1;i<rows;i++){
                row=sheet.getRow(i);
                int c0= (int)(row.getCell(0).getNumericCellValue());
                String c1=row.getCell(1).toString();
                String c2=(row.getCell(2).toString());
                int c3=(int)(row.getCell(3).getNumericCellValue());
                System.out.println(c0+"--"+c1+"--"+c2+"--"+c3);
                Student st=new Student();
                st.setStuid(c0);
                st.setName(c1);
                st.setAddress(c2);
                st.setClassid(c3);
                st.getClassid();
                int ok;
                try {
                    ok=studao.add(st);
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            sheets.close();

    }
    }
